<?php

namespace Lib\Db;

use Lib\Base\Common;
use Lib\Base\Validate;

class Query
{
    private $table;
    private $talias;
    private $where;
    private $updFields;
    private $insFields;
    private $errorInfo;
    private $tableId;

    private $trans = false;

    private $relactionOpt = ['=', '<>', '>', '>=', '<', '<=', 'LIKE', 'IS'];
    private $mathOpt = ['+', '-', '*', '/', '%', '|'];
    private $rangeOpt = ['IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'];

    private $insertSql = '/INSERT/ INTO /TABLE/ /FIELDS/ VALUES /VALUES/';
    private $insUpdSql = 'INSERT INTO /TABLE/ /FIELDS/ VALUES /VALUES/ ON DUPLICATE KEY UPDATE /SET/';
    private $deleteSql = 'DELETE FROM /TABLE/ WHERE /WHERE//ORDER//LIMIT/'; //不允许不带条件进行删除
    private $updateSql = 'UPDATE /TABLE/ SET /SET/ WHERE /WHERE//ORDER//LIMIT/'; //不允许不带条件进行更新
    private $selectSql = 'SELECT /FIELDS/ FROM /TABLE//JOIN//WHERE//GROUP//HAVING//ORDER//LIMIT/';

    private $options = [];
    private $sqlStmt;
    private $joinSubStmt;
    private $transSqlStmt;

    private $dbLink;

    private $filterQuote; //是否过滤掉单引号：true-过滤。，false-不过滤

    private $debug;

    public function __construct($config = ['groupId' => '0', 'shardId' => '', 'shardNum' => '', 'instanceId' => ''])
    {
        $this->dbLink = DbFactory::getDb($config);
        $this->filterQuote = (isset($config['filterQuote']) && $config['filterQuote'] === false) ? false : true;
        $this->initialize();
        $this->errorInfo = ['errno' => 0, 'error' => '', 'data' => ''];
        if (isset($config['debug'])) {
            $this->debug = $config['debug'];
        }
    }

    /**
     * 返回分库分表信息
     * @param string $shardId 用户分库分表的账号ID
     * @param int $tableNum 需要分表数量
     * @return array
     * [
     *      'groupId' => 'db分组ID',
     *      'instanceId' => '实例ID',
     *      'tableId' => '表ID'
     * ]
     */
    public function getShardingInfo($shardId = null, $tableNum = 10)
    {
        $shard = $this->dbLink->getShardingInfo();
        if (is_null($shardId)) {
            $shard['tableId'] = $this->tableId;
        } else {
            $shard['tableId'] = $this->tableRoute($shardId, $tableNum);
        }
        return $shard;
    }

    /**
     * 获取错误信息
     */
    public function getError()
    {
        return $this->errorInfo;
    }

    private function initialize()
    {
        $this->transInit();
        $this->trans = false;
        $this->transSqlStmt = [];
    }

    private function transInit()
    {
        $this->table = '';
        $this->tableId = '';
        $this->talias = [];
        $this->where = [];
        $this->updFields = [];
        $this->insFields = [];
        $this->options = [
            'order' => [],
            'limit' => [],
            'group' => [],
        ];
        $this->sqlStmt = '';
        $this->joinSubStmt = '';
    }

    /**
     * 开启事务
     */
    public function transBegin()
    {
        $this->trans = true;
    }

    /**
     * 执行事务
     *
     * @return bool
     */
    public function transCommit()
    {
        $ret = false;
        if (!empty($this->transSqlStmt) && $this->trans) {
            foreach ($this->transSqlStmt as $sql) {
                $sqlType = strtoupper(explode(' ', $sql)[0]);
                if (!in_array($sqlType, ['INSERT', 'UPDATE', 'REPLACE', 'DELETE'])) {
                    $this->writeLog('-911', '事务只支持 INSERT、UPDATE、REPLACE、DELETE语句！', $sql, 1);
                    return false;
                }
            }
            $ret = $this->dbLink->execTrans($this->transSqlStmt);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
        }
        $this->initialize();
        return $ret;
    }

    /**
     * 指定要查询的表名
     *
     * @param string $table 表名
     * @param string|int $shardId 【不需要分表时不传】用来分表的唯一ID。
     *                   如果传整型（例如QQ号）则直接对$shardNum进行取模。如果传字符串，则通过time33计算之后，在对$shardNum进行取模
     * @param int $shardNum 分表数量，默认分10张表，只有$shardId不为null时，才会生效
     * @return Query
     */
    public function table($table, $shardId = null, $shardNum = 10)
    {
        $this->table = $this->parseTable($table, $shardId, $shardNum);
        if ($this->table === false) {
            return false;
        }
        if (isset($this->talias['__curRealTable__'])) {
            $this->talias[$this->table] = $this->talias['__curRealTable__'];
            unset($this->talias['__curRealTable__']);
        }
        return $this;
    }

    /**
     * 分库分表时直接通过表名和表ID指定要查询的表
     * 
     * @param string $table 表名
     * @param int $tableId 表ID
     * 
     * @return Query
     */
    public function directTable($table, $tableId)
    {
        $this->table = $table . '_' . $tableId;
        if (isset($this->talias['__curRealTable__'])) {
            $this->talias[$this->table] = $this->talias['__curRealTable__'];
            unset($this->talias['__curRealTable__']);
        }
        return $this;
    }

    /**
     * 给表指定别名
     *
     * @param $names
     * @return $this
     */
    public function alias($names)
    {
        recordLog(__METHOD__ . '$name=' . var_export($names, true), __FILE__, __LINE__, LP_INFO);

        if (is_string($names)) {
            if ($this->table) {
                $this->talias[$this->table] = $names;
            } else {
                $this->talias['__curRealTable__'] = $names;
            }
        } else if ($this->isOneDimensionalArray($names)) {
            $keys = explode('_', $this->table);
            if (in_array($keys[0], array_keys($names))) {
                $names[$this->table] = $names[$keys[0]];
                unset($names[$keys[0]]);
            }
            $this->talias = array_merge($this->talias, $names);
        }
        recordLog(__METHOD__ . '$this->talias=' . var_export($this->talias, true), __FILE__, __LINE__, LP_INFO);
        return $this;
    }

    public function buildSql($type = 'select', $data)
    {
    }

    public function field($fields)
    {
    }

    /**
     * 指定排序order字段
     * 示例：
     * $query->order('id', 1)->order(['id1'=>1,'id2'=>0]) - order by id ASC, id1 ASC, id2 DESC
     *
     * @param string|array $fields 需要排序的字段
     * @param int 排序方式，1-升序 0-降序
     * @return $this
     */
    public function order($fields, $order = null)
    {
        if ($this->isFieldName($fields) && in_array($order, ['0', '1'])) {
            $this->options['order'][$fields] = $order;
        } else if ($this->isOneDimensionalArray($fields)) {
            foreach ($fields as $key => $value) {
                if (!$this->isFieldName($key) || !in_array($value, ['0', '1'])) {
                    return $this->initReturn();
                }
            }
            $this->options['order'] = array_merge($this->options['order'], $fields);
        }
        return $this;
    }

    /**
     * 指定分组group by 字段
     *
     * @param $fields
     * @return $this
     */
    public function group($fields)
    {
        if (is_string($fields)) {
            $this->options['group'] = explode(',', $fields);
        } else if ($this->isOneDimensionalArray($fields)) {
            $this->options['group'] = empty($this->options['group']) ? [] : $this->options['group'];
            $this->options['group'] = array_merge($this->options['group'], array_values($fields));
        }
        return $this;
    }

    /**
     * 指定limit
     *
     * @param int $offset
     * @param null $rows
     * @return $this|bool
     */
    public function limit($offset = 1, $rows = null)
    {
        if (!Validate::checkNaturalNum($offset, 1) || (!is_null($rows) && !Validate::checkNaturalNum($rows, 1))) {
            $this->writeLog('-909', 'limit参数不合法', ['offset' => $offset, 'rows' => $rows], 1);
            return false;
        } else {
            $this->options['limit'] = [$offset, $rows];
            return $this;
        }
    }

    /**
     * 执行select查询操作
     * @param string|array 要查询的字段，可以是字符串，也可以是多个字段组成的一维数组
     *
     */
    public function select($fields = null, $addBackQuote = 1)
    {
        $ret = false;
        $selFiled = is_null($fields) ? '*' : $fields;

        if ($this->isFieldName($fields) || $this->isOneDimensionalArray($fields)) {
            $selFiled = $addBackQuote ? $this->addBackQuote($fields) : $fields;
        }

        $this->sqlStmt = str_replace(
            ['/FIELDS/', '/TABLE/', '/JOIN/', '/WHERE/', '/GROUP/', '/HAVING/', '/ORDER/', '/LIMIT/'],
            [
                $selFiled,
                $this->getAliasTable(),
                $this->joinSubStmt,
                $this->buildWhere(1),
                $this->getOptions('group'),
                $this->getOptions('having'),
                $this->getOptions('order'),
                $this->getOptions('limit'),
            ],
            $this->selectSql
        );

        if ($this->trans) {
            $this->transSqlStmt[] = $this->sqlStmt;
            $this->transInit();
        } else {
            $ret = $this->dbLink->execQuery($this->sqlStmt);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
            $this->initialize();
        }
        return $ret;
    }

    /**
     * 执行聚合count
     * @param string $field 要聚合的字段，可以不传，默认count(*)
     * @param string|array $other 要查询的其他字段，可以不传。例：$query->count('id','name'); $query->count('id',['name','age']);
     * @param int $addGroup 是否自动将$other字段进行group by
     * @return int
     *
     */
    public function count($field = '*', $other = null, $addGroup = 0)
    {
        $selFiled = 'COUNT(';
        if ($this->isFieldName($field) && $field != '*') {
            $selFiled .= '`' . $field . '`) AS `count_' . strtolower($field) . '`';
        } else {
            $selFiled .= '*) AS `cnt`';
        }

        if (!is_null($other) && ($this->isFieldName($other) || $this->isOneDimensionalArray($other))) {
            $selFiled .= ', ' . $this->addBackQuote($other);
            if ($addGroup) {
                $this->group($other);
            }
        }

        return $this->select($selFiled, 0);
    }

    /**
     * 执行聚合min
     * @param string|array $field 要聚合的字段。单个字段传字符串，多个字段传一维数组
     * @param string|array $other 要查询的其他字段，可以不传。例：$query->min('id','name'); $query->min(['id', 'age'], ['name','sex']);
     * @param int $addGroup 是否自动将$other字段进行group by
     *
     */
    public function min($field, $other = null, $addGroup = 0)
    {
        return $this->aggregate('min', $field, $other, $addGroup);
    }

    /**
     * 执行聚合max
     * @param string|array $field 要聚合的字段。单个字段传字符串，多个字段传一维数组
     * @param string|array $other 要查询的其他字段，可以不传。例：$query->max('id','name');  $query->max(['id', 'age'], ['name','sex']);
     * @param int $addGroup 是否自动将$other字段进行group by
     *
     */
    public function max($field, $other = null, $addGroup = 0)
    {
        return $this->aggregate('max', $field, $other, $addGroup);
    }

    /**
     * 执行聚合avg
     * @param string|array $field 要聚合的字段。单个字段传字符串，多个字段传一维数组
     * @param string|array $other 要查询的其他字段，可以不传。例：$query->avg('id','name'); $query->avg(['id', 'age'], ['name','sex']);
     * @param int $addGroup 是否自动将$other字段进行group by
     *
     */
    public function avg($field, $other = null, $addGroup = 0)
    {
        return $this->aggregate('avg', $field, $other, $addGroup);
    }

    /**
     * 执行聚合sum
     * @param string|array $field 要聚合的字段。单个字段传字符串，多个字段传一维数组
     * @param string|array $other 要查询的其他字段，可以不传。例：$query->sum('id','name'); $query->sum(['id', 'age'], ['name','sex']);
     * @param int $addGroup 是否自动将$other字段进行group by
     *
     */
    public function sum($field, $other = null, $addGroup = 0)
    {
        return $this->aggregate('sum', $field, $other, $addGroup);
    }

    /**
     * 获取指定查询字段的第一行记录
     * @param array|string $field 字段名称
     * @param bool|string 成功返回第一行数据，如果没有满足条件返回空数组，失败返回false
     */
    public function find($field = null)
    {
        $result = $this->select($field);
        if ($result !== false) {
            if ($result['rows'] > 0) {
                return $result['data'][0];
            } else {
                return [];
            }
        } else {
            return false;
        }
    }

    /**
     * 获取单个指定字段的值
     * @param string $field 字段名称
     * @param bool|string 成功返回字段值，如果没有满足条件的记录返回null，失败返回false
     */
    public function value($field)
    {
        if (!is_string($field) || strpos($field, ',') !== false) {
            $this->writeLog('-912', '要查询的字段必须为单个字段！', 'fieldName=' . var_export($field, true), 1);
            return false;
        }

        $result = $this->select($field);
        if ($result === false) {
            return false;
        }
        if ($result['rows'] > 0) {
            return $result['data'][0][$field];
        } else {
            return null;
        }
    }

    /**
     * 为要执行的SQL语句指定where查询条件，可以多次调用。
     * 当前一次where中的所有字段将会被()包围。并且与其他所有条件以AND进行逻辑连接
     *
     * 示例：
     * 单个字段：
     * $query->where('id',1)->where('age', '>', 18)    id = 1 and age > 18
     * $query->where('age', ['=','18'], ['=','100'], 'or') age = 18 or age = 100
     *
     * $query->where(['id'=>'1'])   id = 1
     * $query->where(['age'=>[['>', 18], ['<', 20]]])->where(['id', ['<', 10], ['>', 20]]) (age > 18 AND age < 20) AND ( id < 10 AND id > 20)
     * 多字段：
     * $query->where(['id'=>['>', '1'], 'age'=>['<', '18']])     where id > '1' and 'age' < '18'
     * $query->where(['id', '>', '1'])
     * $query->where([['id', 'in', [1,2,3]], ['age', '<', '18']])    where id > '1' and 'age' < '18'
     *
     * @param mixed $fields 查询字段相关信息
     * @param mixed $relaction 查询关系运算符
     * @param mixed $values 查询字段的值
     * @param string $logic 当前调用where的各个字段之间的逻辑关系，默认and
     * @return $this
     */
    public function where($fields, $relaction = null, $values = null, $logic = 'and')
    {
        $this->parseWhere('AND', $fields, $relaction, $values, $logic);
        return $this;
    }

    /**
     * where 条件中IN查询
     *
     * 示例：
     * $query->whereIn('id','1,2,3');
     * $query->whereIn('id', [1,2,3]);
     *
     * @param string $field 字段名称
     * @param array|string $value 字段值，格式：一维数组：[1, 2, 3, ...] | 字符串：'1,2,3,...'
     * @return $this|bool 传入格式正确则返回当前对象，否则返回false
     */
    public function whereIn($field, $value, $logic = 'and')
    {
        if ($this->isFieldName($field) && ($this->isFieldValue($value) || $this->isOneDimensionalArray($value))) {
            $this->parseWhere($logic, $field, 'IN', $value);
        } else {
            return $this->initReturn();
        }
        return $this;
    }

    /**
     * where 条件中 NOT IN 查询
     *
     * 示例：
     * $query->whereNotIn('id', '1,2,3');
     * $query->whereNotIn('id', [1, 2, 3]);
     *
     * @param string $field 字段名称
     * @param array|string $value 字段值
     * @return $this|bool 传入格式正确则返回当前对象，否则返回false
     */
    public function whereNotIn($field, $value, $logic = 'and')
    {
        if ($this->isFieldName($field) && ($this->isFieldValue($value) || $this->isOneDimensionalArray($value))) {
            $this->parseWhere($logic, $field, 'NOT IN', $value);
        } else {
            return $this->initReturn();
        }
        return $this;
    }

    /**
     * where 条件中 BETWEEN 查询
     *
     * 示例：
     * $query->whereBetween('id', '1,2');
     * $query->whereBetween('id', [1,2]);
     *
     * @param string $field 字段名称
     * @param array|string $value 字段的值
     * @return $this|bool 传入格式正确则返回当前对象，否则返回false
     */
    public function whereBetween($field, $value, $logic = 'and')
    {
        if (is_string($value) && strpos($value, ',')) {
            $value = explode(',', $value);
        }
        if ($this->isFieldName($field) && (is_array($value) && $this->isOneDimensionalArray($value) && count($value) == 2)) {
            $this->parseWhere($logic, $field, 'BETWEEN', $value);
        }
        return $this;
    }

    /**
     * where 条件中 NOT BETWEEN 查询
     *
     * 示例：
     * $query->whereNotBetween('id', '1,2');
     * $query->whereNotBetween('id', [1,2]);
     *
     * @param string $field 字段名称
     * @param array|string $value 字段的值。数组必须为一维数组，且包含2个值。字符串必须用逗号隔开的2个值
     * @return $this|bool 传入格式正确则返回当前对象，否则返回false
     */
    public function whereNotBetween($field, $value, $logic = 'and')
    {
        if (is_string($value) && strpos($value, ',')) {
            $value = explode(',', $value);
        }
        if ($this->isFieldName($field) && (is_array($value) && $this->isOneDimensionalArray($value) && count($value) == 2)) {
            $this->parseWhere($logic, $field, 'NOT BETWEEN', $value);
        }
        return $this;
    }

    /**
     *  where 条件中的is null
     */
    public function whereNull($field, $logic = 'and')
    {
        $fields = [];
        if (is_string($field)) {
            $fields = strpos($field, ',') !== false ? explode(',', $field) : [$field];
        } else if ($this->isOneDimensionalArray($field)) {
            $fields = $field;
        }

        if (!empty($fields)) {
            $fields = array_map(function ($v) {
                return [$v, 'is', 'null'];
            }, $fields);
            $this->parseWhere($logic, $fields);
        }
        return $this;
    }

    /**
     *  where 条件中的is not null
     */
    public function whereNotNull($field, $logic = 'and')
    {
        $fields = [];
        if (is_string($field)) {
            $fields = strpos($field, ',') !== false ? explode(',', $field) : [$field];
        } else if ($this->isOneDimensionalArray($field)) {
            $fields = $field;
        }

        if (!empty($fields)) {
            $fields = array_map(function ($v) {
                return [$v, 'is not', 'null'];
            }, $fields);
            $this->parseWhere($logic, $fields);
        }
        return $this;
    }

    public function whereOr($fields, $relaction = null, $values = null, $logic2 = 'and')
    {
        $this->parseWhere('OR', $fields, $relaction, $values, $logic2);
        return $this;
    }

    /**
     * 执行insert into 语句
     * 示例：
     *
     * 插入一条：$query->insert(['name1'=>'1','name2'=>'2', ...])
     * 插入多条：$query->insert([['name1'=>'1','name2'=>'2', ...], ['name1'=>'1','name2'=>'2', ...], ...])
     *
     * @param array $data 要插入的数据。插入一条数据时传一维数组，key为字段名称，value为字段值。插入多条数据时传数字下标的二维数组
     * @return int|bool 插入失败：返回false，插入成功：如果有自增ID，则返回自增ID。如果没有自增ID则返回true。一次插入多条返回true
     *
     */
    public function insert($data)
    {
        return $this->executeInsert($data, 'INSERT', 0);
    }

    /**
     * 插入数据并获取自增ID
     */
    public function insertGetId($data)
    {
        return $this->executeInsert($data, 'INSERT', 1);
    }

    /**
     * 执行 replace into 语句
     *
     */
    public function replace($data)
    {
        return $this->executeInsert($data, 'REPLACE', 0);
    }

    /**
     * 执行 INSERT ON DUPLICATE KEY UPDATE
     * @param array $insertData 插入的数据
     * @param array  $updateData 主键重复时更新的字段
     * @return bool|int 成功返回影响函数，失败返回false
     */
    public function insertUpdate($insertData, $updateData)
    {
        $ret = false;
        $this->parseInsert($insertData);
        if (empty($this->insFields)) {
            return false;
        }

        $this->parseUpdate($updateData);
        if (empty($this->updFields)) {
            return false;
        }

        $this->sqlStmt = str_replace(
            ['/TABLE/', '/FIELDS/', '/VALUES/', '/SET/'],
            [
                $this->getAliasTable(),
                $this->insFields['field'],
                implode(',', $this->insFields['values']),
                implode(',', $this->updFields),
            ],
            $this->insUpdSql
        );

        if ($this->trans) {
            $this->transSqlStmt[] = $this->sqlStmt;
            $this->transInit();
        } else {
            $ret = $this->dbLink->execUpdate($this->sqlStmt, 0);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
            $this->initialize();
        }
        return $ret;
    }

    /**
     * 插入数据
     *
     * $query->update(['key1'=>'value1', 'key2'=>'value2', ...])
     * $query->update(['key1'=>['+', '1']]); 对应的更新字段： `key1` = `key1` + 1
     *
     */
    public function update($data = null)
    {
        $ret = false;
        $setStr = '';
        $where = '';
        $this->parseUpdate($data);

        if (!empty($this->updFields)) {
            $setStr = implode(',', $this->updFields);
        }

        if ($setStr == '') {
            return $this->initReturn();
        }

        $where = $this->buildWhere();

        if ($where == '') {
            return $this->initReturn();
        }

        //'UPDATE /TABLE/ SET /SET/ WHERE /WHERE//ORDER//LIMIT/'; //不允许不带条件进行更新
        $this->sqlStmt = str_replace(
            ['/TABLE/', '/SET/', '/WHERE/', '/ORDER/', '/LIMIT/'],
            [
                $this->getAliasTable(),
                $setStr,
                $where,
                $this->getOptions('order'),
                $this->getOptions('limit'),
            ],
            $this->updateSql
        );

        if ($this->trans) {
            $this->transSqlStmt[] = $this->sqlStmt;
            $this->transInit();
        } else {
            $ret = $this->dbLink->execUpdate($this->sqlStmt);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
            $this->initialize();
        }
        return $ret;
    }

    /**
     * 字段递增指定值
     */
    public function incr($name, $num = 1)
    {
        $this->parseUpdate([$name => ['+', $num]]);
        return $this;
    }

    /**
     * 字段递减指定值
     */
    public function decr($name, $num = 1)
    {
        $this->parseUpdate([$name => ['-', $num]]);
        return $this;
    }

    /**
     * 删除数据
     */
    public function delete()
    {
        $ret = false;
        //'DELETE FROM /TABLE/ WHERE /WHERE//ORDER//LIMIT/'; //不允许不带条件进行删除
        $where = $this->buildWhere();
        if ($where == '') {
            return $this->initReturn();
        }

        $this->sqlStmt = str_replace(
            ['/TABLE/', '/WHERE/', '/ORDER/', '/LIMIT/'],
            [
                $this->getAliasTable(),
                $where,
                $this->getOptions('order'),
                $this->getOptions('limit'),
            ],
            $this->deleteSql
        );

        if ($this->trans) {
            $this->transSqlStmt[] = $this->sqlStmt;
            $this->transInit();
        } else {
            $ret = $this->dbLink->execUpdate($this->sqlStmt);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
            $this->initialize();
        }
        return $ret;
    }

    /**
     * 左连接
     */
    public function leftJoin($ljTable, $joinCondition, $otherOn = null)
    {
        $this->join('left join', $ljTable, $joinCondition, $otherOn);
        return $this;
    }

    /**
     * 右连接
     */
    public function rigthJoin($rjTable, $joinCondition, $otherOn = null)
    {
        $this->join('right join', $rjTable, $joinCondition, $otherOn);
        return $this;
    }

    /**
     * 内连接
     */
    public function innerJoin($ijTable, $joinCondition, $otherOn = null)
    {
        $this->join('inner join', $ijTable, $joinCondition, $otherOn);
        return $this;
    }

    /**
     *
     * 执行原生SQL语句
     * @param string $sqlStmt sql语句，sql语句中的动态值用?代替
     * @param array $params sql语句中?对应的值
     *
     */
    public function execute($sqlStmt, $params)
    {
        if ($this->filterQuote) {
            $params = Common::strReplaceDeep(["'", "&#039;", "&apos;"], '', $params);
        }
        $params = Common::addSlashesDeep($params);

        $sql = '';
        $ret = false;

        if ($this->isOneDimensionalArray($sqlStmt) && $this->isTwoDimensionalArray($params) && count($sqlStmt) == count($params)) {
            $transSql = [];
            foreach ($sqlStmt as $index => $item) {
                if ($this->isOneDimensionalArray($params[$index]) && substr_count($item, '?') == count($params[$index])) {
                    $sql = $this->bindParam($item, $params[$index]);
                    if ($sql === false) {
                        return false;
                    } else {
                        $transSql[] = $sql;
                    }
                }
            }
            if (!empty($transSql)) {
                if ($this->trans) {
                    $this->transSqlStmt = \array_merge($this->transSqlStmt, $transSql);
                    $this->transInit();
                } else {
                    $ret = $this->dbLink->execTrans($transSql);
                    $errorInfo = $this->dbLink->getError();
                    $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
                    $this->initialize();
                }
            }
        } else if (is_string($sqlStmt) && substr_count($sqlStmt, '?') == count($params) && !empty($params)) {
            $sql = $this->bindParam($sqlStmt, $params);
            if ($sql === false) {
                return false;
            }

            $sqlType = explode(' ', $sql);
            if (strtoupper($sqlType[0]) == 'SELECT') {
                if ($this->trans) {
                    $this->transSqlStmt[] = $sql;
                    $this->transInit();
                } else {
                    $ret = $this->dbLink->execQuery($sql);
                    $errorInfo = $this->dbLink->getError();
                    $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
                    $this->initialize();
                }
            } else if (in_array(strtoupper($sqlType[0]), ['INSERT', 'UPDATE', 'REPLACE', 'DELETE'])) {
                if ($this->trans) {
                    $this->transSqlStmt[] = $sql;
                    $this->transInit();
                } else {
                    $ret = $this->dbLink->execUpdate($sql);
                    $errorInfo = $this->dbLink->getError();
                    $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
                    $this->initialize();
                }
            }
        } else {
            $this->writeLog('-903', '占位符数量和参数数量不匹配或者参数格式错误，请参考文档。', ['sql' => $sqlStmt, 'params' => $params], 1);
            return false;
        }

        return $ret;
    }

    /**
     * 解析where条件
     * 示例：
     * 单个字段：
     * $query->where('id',1)->where('age', '>', 18)    id = 1 and age > 18
     * $query->where('age', ['=','18'], ['=','100'], 'or') age = 18 or age = 100
     *
     * $query->where(['id'=>'1'])   id = 1
     * $query->where(['age'=>[['>', 18], ['<', 20]]])->where(['id', ['<', 10], ['>', 20]]) (age > 18 AND age < 20) AND ( id < 10 AND id > 20)
     * 多字段：
     * $query->where(['id'=>['>', '1'], 'age'=>['<', '18']])     where id > '1' and 'age' < '18'
     * $query->where(['id', '>', '1'])
     * $query->where([['id', 'in', [1,2,3]], ['age', '<', '18']])    where id > '1' and 'age' < '18'
     *
     */
    private function parseWhere($logic, $fields, $relaction = null, $values = null, $logic2 = 'and')
    {
        $where = '';
        $logic = strtolower($logic);
        $logic2 = strtoupper($logic2);

        if ($this->isFieldName($fields)) {
            //$query->where('name', ['like','%ron%'], ['like','%zheng%'], 'or')
            if (
                $this->isOneDimensionalArray($relaction) && $this->isOneDimensionalArray($values) &&
                count($relaction) == 2 && count($values) == 2 &&
                in_array(strtoupper($relaction[0]), $this->relactionOpt) && in_array(strtoupper($values[0]), $this->relactionOpt)
            ) {
                $where .= $this->parseItem($fields, $relaction[0], $relaction[1]) . ' ' . $logic2 . ' ' . $this->parseItem($fields, $values[0], $values[1]) . ' ' . $logic2 . ' ';
            } else {
                $where .= $this->parseItem($fields, $relaction, $values) . ' ' . $logic2 . ' ';
            }
        } else if ($fields instanceof \Closure) {
        } else if (is_array($fields)) {
            $optArr = array_merge($this->relactionOpt, $this->rangeOpt);
            foreach ($fields as $key => $value) {
                //$query->where(['id'=>'1'])   id = 1
                //$query->where(['age'=>[['>', 18], ['<', 20]]])->where(['id', ['<', 10], ['>', 20]]) (age > 18 AND age < 20) AND ( id < 10 AND id > 20)
                //$query->where(['id'=>['>', '1'], 'age'=>['<', '18']])     where id > '1' and 'age' < '18'
                if ($this->isFieldName($key)) {
                    if ($this->isFieldValue($value)) {
                        //$query->where(['id'=>'1'])
                        $where .= $this->parseItem($key, $value, null) . ' ' . $logic2 . ' ';
                    } else if (is_array($value) && count($value) == 2) {
                        if (is_string($value[0]) && in_array($value[0], $optArr)) {
                            //$query->where(['id'=>['>', '1'], 'age'=>['<', '18']])
                            $where .= $this->parseItem($key, $value[0], $value[1]) . ' ' . $logic2 . ' ';
                        } else if (count($value[0]) == 2 && count($value[1]) == 2 && in_array($value[0][0], $optArr) && in_array($value[1][0], $optArr)) {
                            //$query->where(['age'=>[['>', 18], ['<', 20]]])
                            for ($i = 0; $i < 2; $i++) {
                                $where .= $this->parseItem($key, $value[$i][0], $value[$i][1]) . ' ' . $logic2 . ' ';
                            }
                        }
                    }
                } else {
                    /*$query->table('tbResume')
                    ->where([
                    ['userId', 'in', [1,2,3]],
                    ['flowStatus', 'in', '1,2,3,4'],
                    ['flowId', [1, 2, 10]],
                    ['name', '=', 'xxxxx'],
                    ['comId', '>', 100],
                    ['level', 'between', '1,5'],
                    ['groupId', 'between', [1, 100]],
                    ['auditorId', ['>', 10], ['<', 100]]
                    ])->select();*/
                    if (is_array($value) && (count($value) == 3 || count($value) == 2)) {
                        if (count($value) == 3) {
                            if (is_array($value[1]) && is_array($value[2])) {
                                $where .= $this->parseRangItem($value[0], $value[1], $value[2], $logic2) . ' ' . $logic2 . ' ';
                            } else {
                                $where .= $this->parseItem($value[0], $value[1], $value[2]) . ' ' . $logic2 . ' ';
                            }
                        } else {
                            $where .= $this->parseItem($value[0], $value[1], null) . ' ' . $logic2 . ' ';
                        }
                    }
                }
            }
        }

        if ($where != '') {
            $where = substr($where, 0, 0 - (strlen($logic2) + 2));
            $this->where[$logic][] = $where;
        }
    }

    private function parseRangItem($field, $value1, $value2, $logic)
    {
        $where1 = $this->parseItem($field, $value1[0], $value1[1]);
        $where2 = $this->parseItem($field, $value2[0], $value2[1]);
        return $where1 . ' ' . strtoupper($logic) . ' ' . $where2;
    }

    private function parseItem($field, $relaction = null, $values = null)
    {
        if (is_null($relaction)) {
            return '';
        }
        $field = $this->addBackQuote($field);

        if ($this->filterQuote) {
            $relaction = Common::strReplaceDeep(["'", "&#039;", "&apos;"], '', $relaction);
        }
        $relaction = Common::addSlashesDeep($relaction);

        if (is_null($values)) {
            if ($this->isFieldValue($relaction)) {
                return $field . ' = ' . "'" . $relaction . "'";
            } else if ($this->isOneDimensionalArray($relaction)) {
                return $field . " IN ('" . implode("', '", array_values($relaction)) . "')";
            }
        } else {
            if ($this->filterQuote) {
                $values = Common::strReplaceDeep(["'", "&#039;", "&apos;"], '', $values);
            }
            $values = Common::addSlashesDeep($values);

            $optArr = array_merge($this->relactionOpt, $this->rangeOpt);
            $relaction = \is_string($relaction) ? \strtoupper($relaction) : $relaction;
            if (is_string($relaction) && in_array($relaction, $optArr)) {
                if ($relaction == 'IN' || $relaction == 'NOT IN') {
                    if (is_string($values)) {
                        $values = explode(',', $values);
                    }
                    if ($this->isOneDimensionalArray($values)) {
                        return $field . " {$relaction} ('" . implode("', '", array_values($values)) . "')";
                    }
                } else if ($relaction == 'BETWEEN' || $relaction == 'NOT BETWEEN') {
                    if (is_string($values)) {
                        $values = explode(',', $values);
                    }
                    if ($this->isOneDimensionalArray($values) && count($values) == 2) {
                        return $field . " {$relaction} '" . implode("' AND '", array_values($values)) . "'";
                    }
                } else if ($relaction == 'IS' || $relaction == 'IS NOT') {
                    return $field . " {$relaction} NULL";
                } else {
                    if ($this->isFieldValue($values)) {
                        return $field . " {$relaction} '{$values}'";
                    }
                }
            }
        }
        return '';
    }

    /**
     * 构造where条件
     */
    private function buildWhere($type = 0)
    {
        $where = '';
        if (!empty($this->where)) {
            foreach ($this->where as $logic => $item) {
                foreach ($item as $value) {
                    $where .= strtoupper($logic) . ' (' . $value . ') ';
                }
            }
            $where = trim($where);
            $where = substr($where, (strpos($where, ' ') + 1));
            $andNum = isset($this->where['and']) ? count($this->where['and']) : 0;
            $orNum = isset($this->where['or']) ? count($this->where['or']) : 0;
            if ($andNum + $orNum == 1) {
                $where = substr($where, 1, -1);
            }
        }
        if ($type && $where != '') {
            $where = ' WHERE ' . $where;
        }
        return $where;
    }

    /**
     * 解析update更新数据
     * ['field1'=>'value1', 'field2'=>'value2', 'field3' => ['+', '1']...]
     */
    private function parseUpdate($data)
    {
        if ($this->filterQuote) {
            $data = Common::strReplaceDeep(["'", "&#039;", "&apos;"], '', $data);
        }
        $data = Common::addSlashesDeep($data);
        if (is_array($data)) {
            foreach ($data as $key => $value) {
                if ($this->isFieldName($key)) {
                    if ($this->isFieldValue($value)) {
                        $this->updFields[] = "`{$key}` = '{$value}'";
                    } else if (is_null($value)) {
                        $this->updFields[] = "`{$key}` = NULL";
                    } else if ($this->isOneDimensionalArray($value) && count($value) == 2 && in_array($value[0], $this->mathOpt)) {
                        $this->updFields[] = "`{$key}` = `{$key}` {$value[0]} {$value[1]}";
                    } else {
                        $this->writeLog('-919', '非法表达式，请检查运算符是否在(' . implode(',', $this->mathOpt) . ')中或者字段值是否为数值型、字符串、NULL！', '', 1);
                    }
                }
            }
        }
    }

    /**
     * 解析insert数据
     * 插入一条数据：['field1'=>'value1', 'field2'=>'value2', ....]
     * 插入多条数据：[['field1'=>'value1','field2'=>'value2', ....], ['field1'=>'value1','field2'=>'value2', ....], ...]
     *
     */
    private function parseInsert($data)
    {
        if ($this->filterQuote) {
            $data = Common::strReplaceDeep(["'", "&#039;", "&apos;"], '', $data);
        }
        $data = Common::addSlashesDeep($data);

        if ($this->isOneDimensionalArray($data)) {
            $data = [$data];
        }
        if ($this->isTwoDimensionalArray($data)) {
            $this->insFields['field'] = '(' . $this->addBackQuote(array_keys($data[0])) . ')';
            foreach ($data as $item) {
                $this->insFields['values'][] = '(' . $this->addQute(array_values($item)) . ')';
            }
        }
    }

    /**
     * 构造join查询
     */
    private function join($type, $joinTable, $joinCondition, $otherOn = null)
    {
        if (is_string($joinTable)) {
            $tableArr = [$joinTable];
        } else {
            $tableArr = $joinTable;
        }

        $tableArr[1] = !empty($tableArr[1]) ? $tableArr[1] : null;
        $tableArr[2] = (!empty($tableArr[1]) && empty($tableArr[2])) ? 10 : $tableArr[2];
        $joinTable = $this->parseTable($tableArr[0], $tableArr[1], $tableArr[2]);
        if ($joinTable === false) {
            return false;
        }
        $joinTable = $this->getAliasTable($joinTable);
        if (is_string($joinCondition)) {
            $joinCondition = [$joinCondition];
        }

        $joinCondition = $this->addBackQuote($joinCondition, ' AND ');

        $this->joinSubStmt = ' ' . strtoupper($type) . ' ' . $joinTable . ' ON (' . $joinCondition . ')';
    }

    /**
     * 执行聚合函数相关查询
     * @param string $funcName 聚合函数名称，SUM、MIN、MAX、AVG
     * @param string|array $field 聚合字段。如果要聚合多个字段，传入字段名组成的一维数组，聚合字段都会被命名为聚合函数小写加下划线前缀。例如：sum_total,count_id等
     * @param string|array $other 其他查询字段
     * @param int $addGroup 是否将其他查询字段自动进行group by
     *
     */
    private function aggregate($funcName, $field, $other, $addGroup)
    {
        if (!empty($field)) {
            $selFiled = '';
            if ($this->isFieldName($field)) {
                $selFiled = strtoupper($funcName) . '(`' . $field . '`) AS `' . $funcName . '_' . strtolower($field) . '`';
            } else if ($this->isOneDimensionalArray($field)) {
                foreach ($field as $fd) {
                    $selFiled .= strtoupper($funcName) . '(`' . $fd . '`) AS `' . strtolower($fd) . '_' . strtolower($fd) . '`, ';
                }
                $selFiled = substr($selFiled, 0, -2);
            }

            if (!is_null($other) && ($this->isFieldName($other) || $this->isOneDimensionalArray($other))) {
                $selFiled .= ', ' . $this->addBackQuote($other);
                if ($addGroup) {
                    $this->group($other);
                }
            }
            return $this->select($selFiled, 0);
        }
        return $this->initReturn();
    }

    /**
     * 执行插入操作
     *
     * @param array $data 要插入的数据。插入一条数据时传一维数组，key为字段名称，value为字段值。插入多条数据时传数字下标的二维数组
     * @param string $type INSERT|REPLACE
     * @param int $getId 是否带自增ID 1-是，0-否，如果有自增ID成功会返回自增ID，失败返回false
     * @return int|bool
     */
    private function executeInsert($data, $type = 'INSERT', $getId = 0)
    {
        $ret = false;
        $this->parseInsert($data);
        if (empty($this->insFields)) {
            return false;
        }
        if (!in_array($type, ['INSERT', 'REPLACE'])) {
            return false;
        }
        $this->sqlStmt = str_replace(
            ['/INSERT/', '/TABLE/', '/FIELDS/', '/VALUES/'],
            [$type, $this->getAliasTable(), $this->insFields['field'], implode(', ', $this->insFields['values'])],
            $this->insertSql
        );
        if ($this->trans) {
            $this->transSqlStmt[] = $this->sqlStmt;
            $this->transInit();
        } else {
            $ret = $this->dbLink->execUpdate($this->sqlStmt, $getId);
            $errorInfo = $this->dbLink->getError();
            $this->writeLog($errorInfo['errno'], $errorInfo['error'], $errorInfo['data']);
            $this->initialize();
        }
        return $ret;
    }

    /**
     * 给字符串或者一维数组的值加上单引号
     * @param string|array $fields 要处理的数据
     * @return string 加上单引号的字符串
     */
    private function addQute($values)
    {
        if (is_string($values)) {
            $values = explode(',', $values);
        }
        if ($this->isOneDimensionalArray($values)) {
            return '\'' . implode('\', \'', $values) . '\'';
        } else {
            return '';
        }
    }

    private function getOptions($key)
    {
        if (isset($this->options[$key]) && !empty($this->options[$key])) {
            $option = $this->options[$key];
            if ($key == 'limit') {
                return ' LIMIT ' . $option[0] . (is_null($option[1]) ? '' : ',' . $option[1]);
            } else if ($key == 'order') {
                $str = '';
                foreach ($option as $field => $value) {
                    $str .= '`' . $field . '` ' . ($value == '1' ? 'ASC' : 'DESC') . ', ';
                }
                return ' ORDER BY ' . substr($str, 0, -2);
            } else if ($key == 'group') {
                return ' GROUP BY ' . $this->addBackQuote($option);
            }
        } else {
            return '';
        }
    }

    /**
     * 判断是否为数据库字段名
     */
    private function isFieldName($name)
    {
        if (is_string($name) && !is_numeric($name)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 判断是否为数据库字段值
     */
    private function isFieldValue($value)
    {
        if (is_string($value) || is_numeric($value)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 判断是否为一维数组
     */
    private function isOneDimensionalArray($array)
    {
        if (is_array($array) && count($array) == count($array, COUNT_RECURSIVE)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 判断是否为二维数组
     */
    private function isTwoDimensionalArray($array)
    {
        if (is_array($array)) {
            foreach ($array as $key => $value) {
                if (!$this->isOneDimensionalArray($value)) {
                    return false;
                }
            }
        } else {
            return false;
        }
        return true;
    }

    /**
     * 初始化成员属性并且返回false
     */
    private function initReturn()
    {
        $this->initialize();
        return false;
    }

    /**
     * 给字段、表名加上反单引号
     */
    private function addBackQuote($fields, $implode = ',')
    {
        $rtnData = [];
        if (is_string($fields)) {

            $fieldArr = strpos($fields, ',') !== false ? explode(',', $fields) : [$fields];
        } else {
            $fieldArr = $fields;
        }

        foreach ($fieldArr as $field) {
            if (strpos($field, ' ') !== false) {
                $tmp = explode(' ', $field);
                $tmp = array_map(function ($v) {
                    return trim($v);
                }, $tmp); //防止分割之后有多余的控制
                $field = implode(' ', $tmp);
                $field = str_replace('`', '', $field);
                $tmp = explode(' ', $field);
                if (count($tmp) == 2) {
                    $tv = $tmp[1];
                    $tmp[1] = 'AS';
                    $tmp[2] = $tv;
                }
                if (!preg_match('/\(.*\)/', $tmp[0])) {
                    $tmp[0] = '`' . str_replace('.', '`.`', $tmp[0]) . '`';
                }
                $tmp[2] = '`' . str_replace('.', '`.`', $tmp[2]) . '`';
                $field = implode(' ', $tmp);
            } else {
                $field = str_replace('`', '', $field);
                $field = '`' . str_replace('.', '`.`', $field) . '`';
            }
            $field = str_replace('`*`', '*', $field);
            $rtnData[] = $field;
        }

        return $implode == '' ? $rtnData : implode($implode . ' ', $rtnData);
    }

    /**
     * 根据分库分表规则计算表名
     */
    private function parseTable($table, $shardId, $shardNum)
    {
        $tbTmp = '';
        $this->tableId = '';
        if (!empty($table)) {
            $tbTmp = $table;
            $tbTmp = str_replace('`', '', $tbTmp);
        } else {
            $this->writeLog('-905', '表名不能为空！', '', 1);
            return false;
        }

        if (!is_null($shardId)) {
            $shardNum = preg_match('/^[1-9]\d*$/', $shardNum) ? $shardNum : 10;
            $this->tableId = $this->tableRoute($shardId, $shardNum);
            $tbTmp .= '_' . $this->tableId;
        }

        if (isset($this->talias[$table]) && $tbTmp != $table) {
            $this->talias[$tbTmp] = $this->talias[$table];
            unset($this->talias[$table]);
        }
        return $tbTmp;
    }

    /**
     * 表路由算法 
     */
    private function tableRoute($shardId, $shardNum)
    {
        $tableId =  '';
        if (preg_match('/^[1-9]\d*$/', $shardId)) {
            $tableId = fmod($shardId, $shardNum);
        } else if (is_string($shardId)) {
            $shardId = Common::time33($shardId);
            $tableId = fmod($shardId, $shardNum);
        }
        return $tableId;
    }

    /**
     * 获取带别名的表名
     */
    private function getAliasTable($tb = '')
    {
        $rtnTb = '';
        $tb = $tb == '' ? $this->table : $tb;
        if (array_key_exists($tb, $this->talias)) {
            $rtnTb = $tb . ' AS ' . $this->talias[$tb];
        } else {
            $rtnTb = $tb;
        }

        return $this->addBackQuote($rtnTb);
    }

    /**
     * 绑定参数
     */
    private function bindParam($sqlStmt, $params)
    {
        $matchs = [];
        $limitParams = [];
        $limitStr = '';
        $pattern = '/limit +\?( *, *\?)?/i';

        //不允许执行for update
        if (preg_match('/for +update/i', $sqlStmt) === 1) {
            $this->writeLog('-907', '"FOR UPDATE"不允许执行！', $sqlStmt, 1);
            return false;
        }

        //不允许执行 lock in sharea mode
        if (preg_match('/lock +in +share +mode/i', $sqlStmt) === 1) {
            $this->writeLog('-908', '"LOCK IN SHARE MODE"不允许执行！', $sqlStmt, 1);
            return false;
        }

        if (preg_match($pattern, $sqlStmt, $matchs) === 1) {
            $sqlStmt = preg_replace($pattern, '', $sqlStmt);
        }

        //不允许limit里面直接写偏移量和行数
        if (stripos($sqlStmt, 'limit') !== false && empty($matchs[0])) {
            $this->writeLog('-901', 'limit不允许直接写偏移量和行数，必须通过变量绑定', $sqlStmt, 1);
            return false;
        }

        if (isset($matchs[0])) {
            $num = substr_count($matchs[0], '?');
            for ($i = 0; $i < $num; $i++) {
                $limitParams[] = array_pop($params);
            }
            foreach ($limitParams as $value) {
                if (!Validate::checkNaturalNum($value, 1)) {
                    $this->writeLog('-902', 'limit参数不合法，必须为整数', $limitParams, 1);
                    return false;
                }
            }

            if ($num == 2) {
                $tmp = $limitParams[0];
                $limitParams[0] = $limitParams[1];
                $limitParams[1] = $tmp;
            }
            $limitStr = ' LIMIT ' . implode(', ', $limitParams);
        }

        $search = array_fill(0, count($params), '/\?/');
        $tmpStr = "'" . implode("','", $params) . "'";
        $params = explode(',', $tmpStr);
        $sqlStmt = preg_replace($search, $params, $sqlStmt, 1);

        if ($limitStr != '') {
            $sqlStmt .= $limitStr;
        }
        return $sqlStmt;
    }

    /**
     * 设置错误信息
     */
    private function writeLog($error, $errmsg, $data, $reportLog = 0)
    {
        $this->errorInfo = ['errno' => $error, 'error' => $errmsg, 'data' => $data];
        if ($reportLog == 1) {
            recordLog('Query Log:' . var_export($this->errorInfo, true), __FILE__, __LINE__, LP_ERROR);
        }
    }
}
